package com.dao;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.entity.Employee;
import com.until.UntilClass;
import org.springframework.context.ApplicationContext;

import java.sql.ResultSet;
import java.sql.Statement;

public class EmployeeDaoImpl implements EmployeeDao {
    // 查询一个员工
    @Override
    public Employee selectEmployeeById(int id) {
        // 真实从数据库拿数据，通过druid连接池
        DruidPooledConnection connection = UntilClass.getConnection();
        Statement statement;
        ResultSet resultSet;

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from emp where empno = " + id);
            while (resultSet.next()) {
                ApplicationContext context = UntilClass.getContext();
                Employee emp = context.getBean("Emp", Employee.class);
                // 从容器中拿用户对象 并创建 员工对象打印出来
                emp.setEmpno(resultSet.getInt("empno"));
                emp.setEname(resultSet.getString("ename"));
                emp.setJob(resultSet.getString("job"));
                emp.setMgr(resultSet.getInt("mgr"));
                emp.setHiredate(resultSet.getString("hiredate"));
                emp.setSal(resultSet.getDouble("sal"));
                emp.setComm(resultSet.getDouble("comm"));
                emp.setDeptno(resultSet.getInt("deptno"));

                //  返回查询的员工
                return emp;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        // 关闭连接对象
        UntilClass.connectionClose(resultSet, statement, connection);

        return null;
    }

    // 新增一个员工
    @Override
    public int insertEmployee(Employee employee) {
        // 真实从数据库拿数据，通过druid连接池
        DruidPooledConnection connection = UntilClass.getConnection();
        Statement statement;
        ResultSet resultSet = null;
        int i;

        try {
            statement = connection.createStatement();

            String sql = String.format(
                    "INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (%d, '%s', '%s', %d, '%s', %.2f, %.2f, %d)",
                    employee.getEmpno(),
                    employee.getEname(),
                    employee.getJob(),
                    employee.getMgr(),
                    employee.getHiredate(),
                    employee.getSal(),
                    employee.getComm(),
                    employee.getDeptno()
            );

            i = statement.executeUpdate(sql);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        // 关闭连接对象
        UntilClass.connectionClose(resultSet, statement, connection);

        return i;
    }

    // 修改一个员工
    @Override
    public int updateEmployeeById(Employee employee) {
        // 真实从数据库拿数据，通过druid连接池
        DruidPooledConnection connection = UntilClass.getConnection();
        Statement statement;
        ResultSet resultSet = null;
        int i;

        try {
            statement = connection.createStatement();
            String sql = String.format(
                    "UPDATE emp SET ename = '%s', job = '%s', mgr = %d, hiredate = '%s', sal = %.2f, comm = %.2f, deptno = %d WHERE empno = %d",
                    employee.getEname(),
                    employee.getJob(),
                    employee.getMgr(),
                    employee.getHiredate(),
                    employee.getSal(),
                    employee.getComm(),
                    employee.getDeptno(),
                    employee.getEmpno()
            );


            i = statement.executeUpdate(sql);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        // 关闭连接对象
        UntilClass.connectionClose(resultSet, statement, connection);

        return i;
    }

    // 删除一个员工
    @Override
    public int deleteEmployeeById(int id) {
        // 真实从数据库拿数据，通过druid连接池
        DruidPooledConnection connection = UntilClass.getConnection();
        Statement statement;
        ResultSet resultSet = null;
        int i;

        try {
            statement = connection.createStatement();
            i = statement.executeUpdate("delete from emp where empno = " + id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        // 关闭连接对象
        UntilClass.connectionClose(resultSet, statement, connection);

        return i;
    }
}
